<?php
/**
 * Author: FoxxxZhu
 * Date: 2016/9/6
 * Time: 14:03
 */

namespace App\CLI;

use Forxx\console\CLIRunner;
use Forxx\console\CConsole;

class CMysqlDB extends CLIRunner
{
    private static $sql_file_handle = null;
    private static $sql_tpl_file_handle = null;

    public function export_action()
    {
        $config = $this->get_config();
        $is_copy_data = CConsole::confirm("include data ?");
        foreach($config as $dbname => $config)
        {
            $connect = $config['connect'];
            $dbname_tpl = str_replace("_0","_{{db_index}}",$dbname);
            $is_mutil = isset($config['is_mutil'])?$config['is_mutil']:false;
            $connect = new \Lavender\Db\Mysql($connect['host'],$connect['username'],$connect['password'],$dbname,$connect['port'],$connect['charset']);
            $res = $connect->get_by_sql("show tables");
            $tables = array_column($res,"Tables_in_{$dbname}");
            self::add_to_sql("\n\n-- --------database of {$dbname}----------");
            if($is_mutil)
            {
                self::add_to_sql("use `{$dbname_tpl}`;\n",true);
                $tables = self::get_unique_mutil_tables($tables);
            }
            else
            {
                self::add_to_sql("use `{$dbname}`;\n");
            }
            foreach($tables as $table)
            {
                $res = $connect->get_by_sql("show create table `{$dbname}`.`{$table}`;");
                $create_tables = array_column($res,"Create Table");
                foreach($create_tables as $create_table)
                {
                    if($is_mutil)
                    {
                        $table_tpl = str_replace("_0","_{{table_index}}",$table);
                        $create_table_tpl = str_replace($table,$table_tpl,$create_table);
                        self::add_to_sql("DROP TABLE IF EXISTS `{$table_tpl}`;\n",true);
                        self::add_to_sql($create_table_tpl.";\n",true);
                    }
                    else
                    {
                        self::add_to_sql("DROP TABLE IF EXISTS `{$table}`;\n");
                        self::add_to_sql($create_table.";\n");
                    }
                }

                if($is_copy_data && !$is_mutil)
                {
                    self::add_to_sql("\n\n-- --------data in {$table}----------");
                    $res = $connect->get_by_sql("show columns from `{$dbname}`.`{$table}`;");
                    $columns = array_column($res,"Field");
                    $insert_pre_sql = "INSERT INTO `{$table}` (`".implode("`,`",$columns)."`) VALUES";
                    $times = 0;
                    do{
                        $size = 300;
                        $lines = [];
                        $records = $connect->get_by_sql("select * from `{$table}`",$times * $size , $size);
                        if(count($records)<1)
                        {
                            break;
                        }
                        $times++;
                        foreach($records as $record)
                        {
                            $record = self::escape_array_string($connect->get_link(),$record);
                            $lines[] = "('".implode("','",$record)."')";
                        }
                        if(count($lines)>0)
                        {

                            self::add_to_sql("$insert_pre_sql");
                            self::add_to_sql(implode(",",$lines).";\n");
                        }
                    }while(true);

                }
            }
        }
    }

    public static function escape_array_string($connect,$record)
    {
        $res = [];
        foreach($record as $k => $r)
        {
            $res[$k] = mysqli_real_escape_string($connect,$r);
        }
        return $res;
    }


    protected static function add_to_sql($content,$tpl = false)
    {
        if(empty(self::$sql_file_handle))
        {
            self::$sql_file_handle = fopen(date("YmdH").'.sql','ab+');
        }
        if(empty(self::$sql_tpl_file_handle))
        {
            self::$sql_tpl_file_handle = fopen(date("YmdH").'.tpl.sql','ab+');
        }
        if($tpl)
        {
            fwrite(self::$sql_tpl_file_handle,$content."\n");
        }
        else
        {
            fwrite(self::$sql_file_handle,$content."\n");
        }
    }

    public static function get_unique_mutil_tables($tables)
    {
        $res = [];
        foreach($tables as $table)
        {
            $lenth = strlen($table) - 1;
            $res[] = substr($table,0,$lenth)."0";
        }
        return array_unique($res);
    }



    protected function get_config()
    {
        $connect = ['host'=>'127.0.0.1','username'=>'root','password'=>'zhunian1218','port'=>3306,'charset'=>'utf8'];
        return [
                    'okmnb_base'=>['connect'=>$connect],
                    'okmnb_count'=>['connect'=>$connect],
                    'okmnb_music'=>['connect'=>$connect],
					'okmnb_coreseek'=>['connect'=>$connect],
                    //'okmnb_user'=>['connect'=>$connect],
                    //'okmnb_data_catch'=>['connect'=>$connect],
                    'okmnb_music_0'=>['connect'=>$connect,'is_mutil'=>true],

        ];
    }
}